package top.zhangjianyong.test.reptiles;

import cn.hutool.core.collection.CollectionUtil;
import lombok.extern.slf4j.Slf4j;
import top.zhangjianyong.utils.ConverPercent;
import top.zhangjianyong.utils.JDBCUtil;
import top.zhangjianyong.utils.UrlUtil;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

/**
 * @author ： zhangjianyong
 * @date ： 2023/4/4 10:58 AM
 */
@Slf4j
public class DeleteMain {
    public static void init() throws SQLException {
        String host = "rm-bp11xb28f141i1808ho.rwlb.rds.aliyuncs.com";
        String port = "3306";
        String dbname = "zhangjianyong_tools";
        String url = "jdbc:mysql://" + host + ":" + port + "/" + dbname + "?useSSL=false&allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Hongkong";
        String user = "root";
        String password = "zhangjianyong@2023";
        JDBCUtil.init(url, user, password);
    }

    public static void main(String[] args) {
        try {
            init();
            JDBCUtil.getConnection().setAutoCommit(false);
            Pattern pattern = Pattern
                    .compile("^([hH][tT]{2}[pP]://|[hH][tT]{2}[pP][sS]://)(([A-Za-z0-9-~]+)\\.){2,}([A-Za-z0-9-~]+)");

            int limit = 1000;
            long id = 100000000;
            Object obj = JDBCUtil.qryObj("select max(id) id from reptile_url_suc").get("id");
            if (obj != null) {
                id = (long) obj;
            }
            obj = JDBCUtil.qryObj("select max(id) id from reptile_url_err").get("id");
            if (obj != null) {
                long id2 = (long) obj;
                if (id2 > id) {
                    id = id2;
                }
            }
            obj = JDBCUtil.qryObj("select max(id) id from reptile_url").get("id");
            if (obj != null) {
                long id2 = (long) obj;
                if (id2 > id) {
                    id = id2;
                }
            }

            id++;
            int count = 0;
            PreparedStatement insert = JDBCUtil.getConnection()
                    .prepareStatement("insert ignore into reptile_url(id,url,from_url,remark,state,create_time,update_time) " +
                            "values(?,?,?,?,?,?,?)");
            PreparedStatement delete = JDBCUtil.getConnection()
                    .prepareStatement("delete from reptile_url_0404 where url =?");
            while (true) {
                List<Map<String, Object>> maps = JDBCUtil.qryList("select url,from_url,remark,state,create_time,update_time from reptile_url_0404  order by create_time limit ?", limit);

                if (CollectionUtil.isEmpty(maps)) {
                    log.info("没有数据");
                    return;
                }

                for (Map<String, Object> map : maps) {
                    String url = (String) map.get("url");
                    String url2 = ConverPercent.decode(url).trim().replaceAll(" ", "")
                            .replaceAll("\t", "")
                            .replaceAll("\n", "")
                            .replaceAll("\r", "");
                    if (url2.startsWith("http://.")) {
                        url2 = url2.replace("http://.", "http://");
                    }
                    url2 = UrlUtil.protocol(url2) + "://" + UrlUtil.host(url2);
                    Object from_url = map.get("from_url");
                    Object remark = map.get("remark");
                    Object state = map.get("state");
                    Object create_time = map.get("create_time");
                    Object update_time = map.get("update_time");
                    boolean matches = pattern.matcher(url2).matches();
                    if (matches) {
                        insert.setObject(1, id);
                        insert.setObject(2, url2);
                        insert.setObject(3, from_url);
                        insert.setObject(4, remark);
                        insert.setObject(5, state);
                        insert.setObject(6, create_time);
                        insert.setObject(7, update_time);
                        insert.addBatch();
                    }
                    delete.setObject(1, url);
                    delete.addBatch();
                    id++;
                    //System.out.println(id + ",更新");
                    // System.out.println(num);
                }

                long start = System.currentTimeMillis();
                insert.executeBatch();
                log.debug("INSERT 耗时:" + (System.currentTimeMillis() - start));
                start = System.currentTimeMillis();
                delete.executeBatch();
                log.debug("DELETE 耗时:" + (System.currentTimeMillis() - start));
                start = System.currentTimeMillis();
                //JDBCUtil.commit();
                log.debug("COMMIT 耗时:" + (System.currentTimeMillis() - start));
                count++;
                log.info("第{}次更新提交", count);
            }

        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }

    }

}
